USE master
GO 
/*

DROP DATABASE CollegeWebsite
GO

*/


CREATE DATABASE CollegeWebsite
GO





USE CollegeWebsite
GO


CREATE TABLE [dbo].[Departments]
	(
	[DepartmentID] INT PRIMARY KEY IDENTITY(1,1),
	[DepartmentName] NVARCHAR(50) NULL,
	[DepartmentDescription] NVARCHAR(250) NULL,
	[Status] BIT NOT NULL DEFAULT(1)
	) 
GO

CREATE TABLE [dbo].[Faculties] 
	(
	[FacultyID] INT PRIMARY KEY IDENTITY(1,1),
	[FacultyName] NVARCHAR (50) NOT NULL,
	[FacultyDesciption] NVARCHAR (250) NULL,
	[DepartmentID] INT NOT NULL,
	[Status] bit NOT NULL DEFAULT(0),
	CONSTRAINT FK_Faculties_Department FOREIGN KEY (DepartmentID) REFERENCES dbo.Departments(DepartmentID)
	)

GO


CREATE TABLE [dbo].Courses
(
	[CourseID] INT PRIMARY KEY IDENTITY(1,1),
	[FacultyID] INT,
	[CourseName] NVARCHAR (50) NULL,
	[CourseCapicity] INT NULL,
	[CourseFee] FLOAT (53) NULL,
	[Status] BIT NOT null DEFAULT(1), 
	CONSTRAINT FK_Course_Faculty FOREIGN KEY (FacultyID) REFERENCES dbo.Faculties(FacultyID)
)
GO







CREATE TABLE dbo.Students
(	
	StudentID INT PRIMARY KEY IDENTITY(1,1),
	StudentName nvarchar(50) NULL,
	FatherName nvarchar(50) NULL,
	MotherName nvarchar(50) NULL,
	PIN varchar(20) NOT NULL,
	DateOfBirth datetime NULL,
	Gender BIT NOT NULL DEFAULT(0),
	ResidentialAddress nvarchar(200) NULL,
	PermanentAddress nvarchar(200) NULL,
	AdmissionID INT NULL,
	SupportDetails nvarchar(500) NULL,
	Status BIT NOT NULL DEFAULT(1),
	CONSTRAINT [IX_Student_PassportNumber] UNIQUE ([PIN] ASC)
)
GO

CREATE TABLE dbo.StudentPreviousInfomations
	(
	PreviousInfomationID INT PRIMARY KEY IDENTITY(1,1),
	StudentID INT NOT NULL,
	University nvarchar(250) NULL,
	EnrollmentNo INT NULL,
	Center NCHAR(10) NULL,
	Stream NCHAR(10) NULL,
	Field NCHAR(10) NULL,
	MarksSecured NCHAR(10) NULL,
	OutOfDate datetime NULL,
	ClassObitained NCHAR(10) NULL,
	Status BIT NOT NULL
 CONSTRAINT	FK_StudentPreviousInfomations_Students FOREIGN KEY	(StudentID) REFERENCES dbo.Students (StudentID)
	) 
GO




CREATE TABLE dbo.Facilities
	(
	FacilityID INT PRIMARY Key IDENTITY(1,1),
	FacilityName nvarchar(100) NULL,
	FacilityDescription nvarchar(MAX) NULL,
	FacilityCapicity INT NULL,
	FacilityFee FLOAT(53) NULL
	)
GO


CREATE TABLE [dbo].[StudentFacility] (
[FacilityID] int NOT NULL,
[StudentID] int NOT NULL,
CONSTRAINT FK_StudentFacility_StudentID FOREIGN KEY (StudentID) REFERENCES dbo.Students(StudentID),
CONSTRAINT FK_StudentFacility_FacilityID FOREIGN KEY (FacilityID) REFERENCES dbo.Facilities(FacilityID)
)
GO


CREATE TABLE dbo.Subjects
	(
	SubjectID INT PRIMARY KEY IDENTITY(1,1),
	SubjectName nvarchar(150) NULL,
	SujectDescription nvarchar(250) NULL,
	CourseID VARCHAR(150) NULL,
	Status BIT NULL
	)
GO








CREATE table [dbo].[Admissions](
	[CourseID] int,
	[StudentID] int ,
	[Status] bit NOT null DEFAULT(0),
CONSTRAINT FK_Admission_Courses FOREIGN KEY (CourseID) REFERENCES dbo.Courses(CourseID),
CONSTRAINT FK_Admission_Student FOREIGN KEY (StudentID) REFERENCES dbo.Students(StudentID)
)
GO

CREATE TABLE dbo.StudentSubject
	(
	StudentSubjectID INT PRIMARY KEY IDENTITY(1,1),
	StudentID INT NULL,
	SubjectID INT NOT NULL,
	CONSTRAINT FK_StudentSubject_Students FOREIGN KEY(StudentID) REFERENCES dbo.Students(StudentID),
	CONSTRAINT FK_StudentSubject_Subjects FOREIGN KEY(SubjectID) REFERENCES dbo.Subjects(SubjectID)
	)

GO


CREATE TABLE [dbo].[Menus](
[MenuID] int primary key identity(1,1),
[ParentID] int not null DEFAULT(0),
[Level] int DEFAULT(0),
[MenuName] nvarchar(50) not null,
[TextContent] nvarchar(100) not null,
[Link] nvarchar(200),
[Icons] nvarchar(200),
[Publish] bit not null DEFAULT(0)
)




create table Admins(
AdminID int PRIMARY key IDENTITY(1,1),
UserName VARCHAR(50) not null,
PassWord varchar(32) not NULL,
AdminName nvarchar(100) not null, 
Status bit not NULL DEFAULT(1),
CONSTRAINT [UQ_Admins_UserName] UNIQUE ([UserName] ASC)
)







